
if exists(select name from sysobjects where name = 'sp_DiemGetChuongTrinhDT')
	drop proc sp_DiemGetChuongTrinhDT
go
/*
	Created by: Phuoc NH
	Created on: 13/0/2013 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_DiemGetChuongTrinhDT.sql
*/

create proc sp_DiemGetChuongTrinhDT
@MaSinhVien varchar(50)
as
begin
declare @a table(MaSinhVien varchar(50), MaMon varchar(50), TenMon Nvarchar(200), SOTC tinyint)
insert into @a select a.MaSinhVien, d.MaMon, e.TenMon, d.SoTC from SinhVien a inner join Lop b on a.MaLop=b.MaLop inner join ChuongTrinhDT c on b.MaCT=c.MaCT inner join ChuongTrinhCT d on c.MaCT=d.MaCT inner join Mon e on e.MaMon=d.MaMon where a.MaSinhVien=@MaSinhVien
select  a.MaMon, a.TenMon, b.Diem, a.SOTC from  @a a left OUTER join BangDiem  b on a.MaMon=b.MaMon and b.MaSinhVien=@MaSinhVien
end


